/**
 * Copyright 2008-2009. Chongqing Communications Industry Services Co.,Ltd Information Technology Branch. All rights
 * reserved. <a>http://www.cqcis.com</a>
 */
package com.cqcis.uip.base.framework.utils;

import java.beans.PropertyDescriptor;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.math.BigInteger;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.WeakHashMap;

import javax.persistence.Column;
import javax.persistence.Table;

import org.apache.commons.dbutils.BeanProcessor;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.jdbc.core.simple.ParameterizedRowMapper;
import org.springframework.jdbc.core.simple.SimpleJdbcDaoSupport;
import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;
import org.springframework.jdbc.core.support.JdbcDaoSupport;

import com.cqcis.uip.base.framework.adaptor.SimpleJdbcTemplateAdaptor;
import com.cqcis.uip.base.framework.utils.ObjectUtil.TypeValue;

/**
 * 数据库操作工具, 封装JDBC方法, 支持泛型
 * 
 * @author huangmy
 * @date   2008-6-16
 */
public class JdbcUtil {	
	private static final int OBJECT_COLUME_INDEX = 1; // 获取对象位置索引
	private static BeanProcessor beanProcessor = null;
	private static final Map<SimpleJdbcTemplate, SimpleJdbcTemplateAdaptor> sjts = new WeakHashMap<SimpleJdbcTemplate, SimpleJdbcTemplateAdaptor>();
	
	static {
	    beanProcessor = new BeanProcessor() {
	    	@Override
	    	protected int[] mapColumnsToProperties(ResultSetMetaData rsmd, PropertyDescriptor[] props)
	    			throws SQLException {	    		
	    		int[] columnToProperty = super.mapColumnsToProperties(rsmd, props);
	    		if (columnToProperty != null) {
	    			for (int col = 1; col <= columnToProperty.length - 1; col++) {
	    				String columnName = rsmd.getColumnName(col);
	    				if (col == PROPERTY_NOT_FOUND) {
	    					throw new SQLException("未找到对应的类型属性名: " + columnName);
	    				}
	    			}
	    		}
	    		
	    		return columnToProperty;
	    	}
	    	
	    	@SuppressWarnings("unchecked")
			@Override
	    	protected Object processColumn(ResultSet rs, int index, Class propType) throws SQLException {
	    		if (propType.equals(BigDecimal.class)) {
	    			return rs.getBigDecimal(index);
	    		}	    		
	    		return super.processColumn(rs, index, propType);
	    	}
	    };
	}
	
	/**
	 * 转换, 将 ResultSet 转换为 clazzTo 的对象
	 * 
	 * @param rsFrom
	 * @param clazzTo
	 * @return JdbcUtil
	 */
    @SuppressWarnings("unchecked")
	public static <T> T convert(ResultSet rsFrom, Class<T> clazzTo) {
    	ObjectUtil.assertParamsNotNULL(rsFrom, clazzTo);
    	
    	try {
			return (T)beanProcessor.toBean(rsFrom, clazzTo);
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
    }
    
    /**
     * 获取模板
     * 
     * @param sjt
     * @return
     */
    private static SimpleJdbcTemplate createOrGetSJT(SimpleJdbcTemplate sjt) {
    	if (sjt == null) {
    		return null;
    	}
    	
    	SimpleJdbcTemplateAdaptor s = sjts.get(sjt);
    	if (s == null) {
    		s =  new SimpleJdbcTemplateAdaptor(sjt);    		
    		sjts.put(sjt, s);
    	}
    	
    	return s;
    }
    
    /**
     * 保存
     * 
     * @param dao
     * @param obj
     */
    public static void insert(SimpleJdbcDaoSupport dao, Object bean) {
    	if (bean == null) {
    		throw new NullPointerException("the obj can't be null.");
    	}
    	update(createInsertSQLForClassEntity(bean.getClass(), null), bean, dao);
    }
    
    /**
     * 保存
     * 
     * @param dao
     * @param bean
     * @param overrideParams  指定某个字段的值， 覆盖bean的值
     */
    public static void insert(SimpleJdbcDaoSupport dao, Object bean, Map<String, Object> overrideParams) {
    	if (bean == null) {
    		throw new NullPointerException("the obj can't be null.");
    	}
    	update(createInsertSQLForClassEntity(bean.getClass(), overrideParams), bean, dao);
    }
    
    /**
     * 根据JPA注解生成SQL前缀
     * 
     * @param clazz
     * @return
     */
    private static String createInsertSQLForClassEntity(Class<?> clazz, Map<String, Object> overrideParams) {
    	StringBuffer sql = new StringBuffer();
    	sql.append("insert into ");
    	Table table = (Table)AnnotationUtil.getAnnotationForClass(clazz, Table.class);
    	if (table == null) {
    		sql.append(clazz.getSimpleName());
    	} else {
    		sql.append(table.name());
    	}    
    	sql.append("(");
    	StringBuffer params = new StringBuffer();
    	Method[] methods = AnnotationUtil.findAnnotationMethodsFor(clazz, Column.class);
    	if (methods != null) {
    		for (int i = 0; i < methods.length; i ++) {
    			Method m = methods[i];
    			Column c = (Column)AnnotationUtil.getAnnotationForMethod(m, Column.class);
    			sql.append(c.name());
    			String methodname = m.getName();
    			methodname = methodname.substring(3, 4).toLowerCase() + methodname.substring(4);
    			if (overrideParams != null && overrideParams.containsKey(methodname)) {
    				params.append(overrideParams.get(methodname)); 
    			} else {
    			    params.append(":" + methodname);
    			}
    			if (i < methods.length - 1) {
    				sql.append(",");
    				params.append(",");
    			}
    		}
    	}
    	sql.append(") values(");
    	sql.append(params.toString());
    	sql.append(")");
    	    	
    	return sql.toString();
    }
    
    /**
     * 在指定的jdbcTemplate上用指定的insert、update SQL保持对象或者delete执行删除语句
     * 
     * @param insertOrUpdateOrDeleteSql
     * @param bean
     * @param sjt
     */
    public static void update(String insertOrUpdateOrDeleteSql, Object bean, SimpleJdbcTemplate sjt) {
    	updateWithReply(insertOrUpdateOrDeleteSql, bean, sjt);
    }
    
    /**
     * 返回影响的行数
     * 
     * @param insertOrUpdateOrDeleteSql
     * @param bean
     * @param sjt
     * @return
     */
    public static int updateWithReply(String insertOrUpdateOrDeleteSql, Object bean, SimpleJdbcTemplate sjt) {
    	ObjectUtil.assertParamsNotNULL(insertOrUpdateOrDeleteSql, bean, sjt);
    	
    	sjt = createOrGetSJT(sjt);
    	Map<String, Object> params = ObjectUtil.convert(bean);
    	
    	int rows = 0;
    	try {
    		rows = sjt.update(insertOrUpdateOrDeleteSql, params);
    	} catch (Throwable t) {    		
    		throw new RuntimeException(t.getMessage() + " VALUES: " + params,t.getCause());
    	}
    	
    	return rows;
    }
    
    /**
     * 在指定的jdbcTemplate上用指定的insert、update SQL保持对象或者delete执行删除语句,
     * 其中参数值必须为基本类型, 如String, int, short, long, float, double, byte, boolean.
     * 
     * @param jt
     * @param insertOrUpdateOrDeleteSql
     * @param params
     */
    public static void update(JdbcTemplate jt, String insertOrUpdateOrDeleteSql, Object...params) {
    	updateWithReply(jt, insertOrUpdateOrDeleteSql, params);
    }
    
    /**
     * 返回影响的行数
     * 
     * @param jt
     * @param insertOrUpdateOrDeleteSql
     * @param params
     * @return
     */
    public static int updateWithReply(JdbcTemplate jt, String insertOrUpdateOrDeleteSql, Object...params) {
    	ObjectUtil.assertParamsNotNULL(insertOrUpdateOrDeleteSql, params, jt);
    	PreparedStatementSetter pss = translatePSSFor(params);
    	
    	int rows = 0;
    	try {
			if (pss == null) {
				rows = jt.update(insertOrUpdateOrDeleteSql);
			} else {
				rows = jt.update(insertOrUpdateOrDeleteSql, pss);
			}
		} catch (Throwable t) {			
			StringBuffer b = new StringBuffer();
			b.append("[");
			for (Object param : params) {
				b.append(param).append(",");
			}
			b.append("]");
			throw new RuntimeException(t.getMessage() + " VALUES: " + b.toString(),t.getCause());
		}
		
		return rows;
    }
    
    /**
     * 在指定的jdbcTemplate上用指定的insert、update SQL保持对象
     * 
     * @param insertOrUpdateOrDeleteSql
     * @param bean
     * @param dao
     */
    public static void update(String insertOrUpdateOrDeleteSql, Object bean, SimpleJdbcDaoSupport dao) {
    	update(insertOrUpdateOrDeleteSql, bean, dao.getSimpleJdbcTemplate());
    }
    
    /**
     * 返回影响的行数
     * 
     * @param insertOrUpdateOrDeleteSql
     * @param bean
     * @param dao
     * @return
     */
    public static int updateWithReply(String insertOrUpdateOrDeleteSql, Object bean, SimpleJdbcDaoSupport dao) {
    	return updateWithReply(insertOrUpdateOrDeleteSql, bean, dao.getSimpleJdbcTemplate());
    }
    
    /**
     * 在指定的jdbcTemplate上用指定的insert、update SQL保持对象或者delete执行删除语句,
     * 其中参数值必须为基本对象, 如String, int, short, long, float, double, byte, boolean.
     * 
     * @param dao
     * @param insertOrUpdateOrDeleteSql
     * @param params
     */
    public static void update(JdbcDaoSupport dao, String insertOrUpdateOrDeleteSql, Object...params) {
    	update(dao.getJdbcTemplate(), insertOrUpdateOrDeleteSql, params);
    }
    
    /**
     * 返回影响的行数
     * 
     * @param dao
     * @param insertOrUpdateOrDeleteSql
     * @param params
     * @return
     */
    public static int updateWithReply(JdbcDaoSupport dao, String insertOrUpdateOrDeleteSql, Object...params) {
    	return updateWithReply(dao.getJdbcTemplate(), insertOrUpdateOrDeleteSql, params);
    }
    
    /**
     * 在指定的jdbcTemplate上用指定的insert、update SQL保持对象
     * 
     * @param insertOrUpdateOrDeleteSql
     * @param bean
     * @param sjt
     */
    public static void update(SimpleJdbcTemplate sjt, String insertOrUpdateOrDeleteSql, Map<String, Object> props) {
    	updateWithReply(sjt, insertOrUpdateOrDeleteSql, props);
    }
    
    /**
     * 返回影响的行数
     * 
     * @param sjt
     * @param insertOrUpdateOrDeleteSql
     * @param props
     * @return
     */
    public static int updateWithReply(SimpleJdbcTemplate sjt, String insertOrUpdateOrDeleteSql, Map<String, Object> props) {
    	ObjectUtil.assertParamsNotNULL(insertOrUpdateOrDeleteSql, props, sjt);
    	sjt = createOrGetSJT(sjt);
    	int rows = 0;
    	try {
    		rows = sjt.update(insertOrUpdateOrDeleteSql, props);
		} catch (Throwable t) {			
			throw new RuntimeException(t.getMessage() + " VALUES: " + props,t.getCause());
		}
		
		return rows;
    }
    
    /**
     * 在指定的jdbcTemplate上用指定的insert、update SQL保持对象
     * 
     * @param insertOrUpdateOrDeleteSql
     * @param bean
     * @param dao
     */
    public static void update(SimpleJdbcDaoSupport dao, String insertOrUpdateOrDeleteSql, Map<String, Object> props) {
    	update(dao.getSimpleJdbcTemplate(), insertOrUpdateOrDeleteSql, props);
    }
    
    /**
     * 返回影响的行数
     * 
     * @param dao
     * @param insertOrUpdateOrDeleteSql
     * @param props
     * @return
     */
    public static int updateWithReply(SimpleJdbcDaoSupport dao, String insertOrUpdateOrDeleteSql, Map<String, Object> props) {
    	return updateWithReply(dao.getSimpleJdbcTemplate(), insertOrUpdateOrDeleteSql, props);
    }
    
    /**
     * 返回clazzTo参数指定的查询对象, 如果没有，则返回null
     * 
     * @param jt
     * @param clazzTo
     * @param querySql
     * @param pss
     * @return
     */
    @SuppressWarnings("unchecked")
	public static <T> T queryForObject(JdbcTemplate jt, final Class<T> clazzTo, String querySql, PreparedStatementSetter pss) {
    	List<T> list = (List<T>)queryForList(jt, clazzTo, querySql, pss);
    	return list.size() > 0 ? list.get(0) : null;
    }
    
    /**
     * 返回clazzTo参数指定的查询对象, 如果没有，则返回null
     * 
     * @param dao
     * @param clazzTo 必须为JavaBean
     * @param querySql
     * @param pss
     * @return
     */
    @SuppressWarnings("unchecked")
	public static <T> T queryForObject(JdbcDaoSupport dao, final Class<T> clazzTo, String querySql, PreparedStatementSetter pss) {
    	return queryForObject(dao.getJdbcTemplate(), clazzTo, querySql, pss);
    }
    
    /**
     * 返回clazzTo参数指定的查询对象, 如果没有，则返回null
     * 
     * @param jt
     * @param clazzTo
     * @param querySql
     * @return
     */
    @SuppressWarnings("unchecked")
	public static <T> T queryForObject(JdbcTemplate jt, final Class<T> clazzTo, String querySql, Object...params) {
    	List<T> list = (List<T>)queryForList(clazzTo, jt, querySql, params);
    	return list.size() > 0 ? list.get(0) : null;
    }
    
    /**
     * 返回clazzTo参数指定的查询对象, 如果没有，则返回null
     * 
     * @param dao
     * @param clazzTo
     * @param querySql
     * @return
     */
    @SuppressWarnings("unchecked")
	public static <T> T queryForObject(JdbcDaoSupport dao, final Class<T> clazzTo, String querySql, Object...params) {
    	return queryForObject(dao.getJdbcTemplate(), clazzTo, querySql, params);
    }
    
    /**
     * 返回clazzTo参数指定的查询对象, 如果没有，则返回null
     * 
     * @param dao
     * @param clazzTo
     * @param conditionQuerySql 必须形如where ....，可以为null
     * @param params
     * @return
     */
    public static <T> T queryForEntity(JdbcDaoSupport dao, final Class<T> clazzTo, String conditionQuerySql, Object...params) {
    	// 1. 生成select SQL
    	conditionQuerySql = conditionQuerySql != null ? conditionQuerySql : "";
    	String querySql = createSQLPreForClassEntity(clazzTo) + conditionQuerySql;
    	// 2. 查询
    	return queryForObject(dao, clazzTo, querySql, params);
    }
    
    /**
     * 返回clazzTo参数指定的查询对象, 返回第一条数据, 如果没有，则返回null
     * 
     * @param dao
     * @param clazzTo
     * @param params
     * @return
     */
    public static <T> T queryForEntity(JdbcDaoSupport dao, final Class<T> clazzTo) {
    	// 1. 生成select SQL
    	String querySql = createSQLPreForClassEntity(clazzTo);
    	// 2. 查询
    	return queryForEntity(dao, clazzTo, querySql);
    }
    
    /**
     * 根据JPA注解生成SQL前缀
     * 
     * @param clazz
     * @return
     */
    private static String createSQLPreForClassEntity(Class<?> clazz) {
    	StringBuffer sql = new StringBuffer();
    	sql.append("select ");
    	Method[] methods = AnnotationUtil.findAnnotationMethodsFor(clazz, Column.class);
    	if (methods != null) {
    		for (int i = 0; i < methods.length; i ++) {
    			Method m = methods[i];
    			Column c = (Column)AnnotationUtil.getAnnotationForMethod(m, Column.class);
    			sql.append(c.name());
    			sql.append(" as ");
    			sql.append(getMethodFieldName(m));
    			if (i < methods.length - 1) {
    				sql.append(",");
    			}
    		}
    	}
    	sql.append(" ");
    	sql.append("from ");
    	Table table = (Table)AnnotationUtil.getAnnotationForClass(clazz, Table.class);
    	if (table == null) {
    		sql.append(clazz.getSimpleName());
    	} else {
    		sql.append(table.name());
    	}    
    	sql.append(" ");
    	//System.out.println(sql.toString());
    	return sql.toString();
    }
    
    private static String getMethodFieldName(Method method) {
    	String name = method.getName();
    	name = name.substring(name.indexOf("get") + 3);
    	char[] cs = name.toCharArray();
    	char c = cs[0];
    	c = Character.toUpperCase(c);
    	cs[0] = c;
    	
    	return new String(cs);
    }
    
    /**
     * SIMPLE
     * 
     * @param dao
     * @param clazzTo
     * @param querySql
     * @param params
     * @return
     */
    public static <T> T queryForObject(SimpleJdbcDaoSupport dao, final Class<T> clazzTo, String querySql, Object...params) {
    	return queryForObject(dao.getJdbcTemplate(), clazzTo, querySql, params);
    }
    
    /**
     * SIMPLE
     * 
     * @param dao
     * @param clazzTo
     * @param conditionQuerySql
     * @param params
     * @return
     */
    public static <T> T queryForEntity(SimpleJdbcDaoSupport dao, final Class<T> clazzTo, String conditionQuerySql, Object...params) {
    	// 1. 生成select SQL
    	conditionQuerySql = conditionQuerySql != null ? conditionQuerySql : "";
    	String querySql = createSQLPreForClassEntity(clazzTo) + conditionQuerySql;
    	// 2. 查询
    	return queryForObject(dao, clazzTo, querySql, params);
    }
    
    /**
     * SIMPLE
     * 
     * @param dao
     * @param clazzTo
     * @return
     */
    public static <T> T queryForEntity(SimpleJdbcDaoSupport dao, final Class<T> clazzTo) {
    	// 1. 生成select SQL
    	String querySql = createSQLPreForClassEntity(clazzTo);
    	// 2. 查询
    	return queryForEntity(dao, clazzTo, querySql);
    }
    
    /**
     * 返回clazzTo参数指定的查询对象, 如果没有，则返回null
     * 
     * @param sjt
     * @param clazzTo
     * @param querySql
     * @param paramsBean
     * @return
     */
    @SuppressWarnings("unchecked")
	public static <T> T queryForObject(SimpleJdbcTemplate sjt, final Class<T> clazzTo, String querySql, Object paramsBean) {
    	sjt = createOrGetSJT(sjt);
    	
    	List<T> list = (List<T>)queryForList(sjt, clazzTo, querySql, paramsBean);
    	return list.size() > 0 ? list.get(0) : null;
    }
    
    /**
     * 返回clazzTo参数指定的查询对象, 如果没有，则返回null
     * 
     * @param dao
     * @param clazzTo
     * @param querySql
     * @param paramsBean
     * @return
     */
    @SuppressWarnings("unchecked")
	public static <T> T queryForObject(final Class<T> clazzTo, String querySql, Object paramsBean, SimpleJdbcDaoSupport dao) {
    	return queryForObject(dao.getSimpleJdbcTemplate(), clazzTo, querySql, paramsBean);
    }
        
    /**
     * 返回一个查询列表, 列表中的对象类型为clazzTo参数指定的类型对象。
     * 改方法适用于查询SQL中使用?作为参数占位符的SQL语句
     * 
     * @param querySql
     * @param clazzTo
     * @param dao
     * @return
     */
    public static <T> List<T> queryForList(JdbcDaoSupport dao, final Class<T> clazzTo, String querySql, Object...params) {    	
    	return queryForList(clazzTo, dao.getJdbcTemplate(), querySql, params);
    }
    
    /**
     * 返回一个查询列表, 列表中的对象类型为clazzTo参数指定的类型对象。
     * 改方法适用于查询SQL中使用?作为参数占位符的SQL语句
     * 
     * @param dao
     * @param clazzTo
     * @param conditionQuerySql
     * @param params
     * @return
     */
    public static <T> List<T> queryForEntityList(JdbcDaoSupport dao, final Class<T> clazzTo, String conditionQuerySql, Object...params) {    	
    	// 1. 生成select SQL
    	conditionQuerySql = conditionQuerySql != null ? conditionQuerySql : "";
    	String querySql = createSQLPreForClassEntity(clazzTo) + conditionQuerySql;
    	// 2. 查询
    	return queryForList(dao, clazzTo, querySql, params);
    }
    
    /**
     * 不带查询条件，获取所有的记录。
     * 
     * @param dao
     * @param clazzTo
     * @return
     */
    public static <T> List<T> queryForEntityList(JdbcDaoSupport dao, final Class<T> clazzTo) {    	
    	// 1. 生成select SQL
    	String querySql = createSQLPreForClassEntity(clazzTo);
    	// 2. 查询
    	return queryForEntityList(dao, clazzTo, querySql);
    }
    
    /**
     * SIMPLE
     * 
     * @param dao
     * @param clazzTo
     * @param querySql
     * @param params
     * @return
     */
    public static <T> List<T> queryForList(SimpleJdbcDaoSupport dao, final Class<T> clazzTo, String querySql, Object...params) {  
    	return queryForList(clazzTo, dao.getJdbcTemplate(), querySql, params);
    }
    
    /**
     * SIMPLE
     * 
     * @param dao
     * @param clazzTo
     * @param conditionQuerySql
     * @param params
     * @return
     */
    public static <T> List<T> queryForEntityList(SimpleJdbcDaoSupport dao, final Class<T> clazzTo, String conditionQuerySql, Object...params) {  
    	// 1. 生成select SQL
    	conditionQuerySql = conditionQuerySql != null ? conditionQuerySql : "";
    	String querySql = createSQLPreForClassEntity(clazzTo) + conditionQuerySql;
    	// 2. 查询
    	return queryForList(dao, clazzTo, querySql, params);
    }
    
    /**
     * SIMPLE QUERY ALL
     * 
     * @param dao
     * @param clazzTo
     * @return
     */
    public static <T> List<T> queryForEntityList(SimpleJdbcDaoSupport dao, final Class<T> clazzTo) {  
    	// 1. 生成select SQL
    	String querySql = createSQLPreForClassEntity(clazzTo);
    	// 2. 查询
    	return queryForEntityList(dao, clazzTo, querySql);
    }
    
    /**
     * 返回一个查询列表, 列表中的对象类型为clazzTo参数指定的类型对象。
     * 改方法适用于查询SQL中使用?作为参数占位符的SQL语句，用户必须手工
     * 构建一个PreparedStatementSetter.
     * 
     * @param querySql
     * @param pss
     * @param clazzTo
     * @param jt
     * @return
     */
    public static <T> List<T> queryForList(JdbcTemplate jt, final Class<T> clazzTo, String querySql, PreparedStatementSetter pss) {
    	ObjectUtil.assertParamsNotNULL(querySql, pss, clazzTo, jt);
    	
		final List<T> list = new ArrayList<T>();

		jt.query(querySql, pss, new RowCallbackHandler() {
			@SuppressWarnings("unchecked")
			public void processRow(ResultSet rs) throws SQLException {
				Object bean = null;
				if (isPrivimateType(clazzTo)) {
					bean = getPrivimateType(clazzTo, rs);
				} else {
				    bean = (Object) JdbcUtil.convert(rs, clazzTo);
				}
				
				if (bean != null) {
					list.add((T)bean);
				}
			}
		});

		return list;
	}
    
    /**
     * 返回一个查询列表, 列表中的对象类型为clazzTo参数指定的类型对象。
     * 其中参数值必须为基本类型, 如String, int, short, long, float, double, byte, boolean.
     * 
     * @param clazzTo
     * @param jt
     * @param querySql
     * @param params
     * @return
     */
    public static <T> List<T> queryForList(final Class<T> clazzTo, JdbcTemplate jt, String querySql, Object... params) {
    	ObjectUtil.assertParamsNotNULL(querySql, params, clazzTo, jt);// 检查非空参数    	
    	
    	PreparedStatementSetter pss = translatePSSFor(params);
    	if (pss == null) {
    		final List<T> list = new ArrayList<T>();

    		jt.query(querySql, new RowCallbackHandler() {
    			@SuppressWarnings("unchecked")
				public void processRow(ResultSet rs) throws SQLException {
    				Object bean = null;
    				if (isPrivimateType(clazzTo)) {
    					bean = getPrivimateType(clazzTo, rs);
    				} else {
    				    bean = (Object) JdbcUtil.convert(rs, clazzTo);
    				}
    				
    				if (bean != null) {
    					list.add((T)bean);
    				}
    			}
    		});

    		return list;
    	}
    	
    	return queryForList(jt, clazzTo, querySql, pss);
    }
    
    /**
     * 返回一个查询列表, 列表中的对象类型为clazzTo参数指定的类型对象。
     * 改方法适用于查询SQL中使用?作为参数占位符的SQL语句，用户必须手工
     * 构建一个PreparedStatementSetter.
     * 
     * @param querySql
     * @param pss
     * @param clazzTo
     * @param dao
     * @return
     */
    public static <T> List<T> queryForList(JdbcDaoSupport dao, final Class<T> clazzTo, String querySql, PreparedStatementSetter pss) {
    	return queryForList(dao.getJdbcTemplate(), clazzTo, querySql, pss);
    }
    
    /**
     * 返回一个查询列表, 列表中的对象类型为clazzTo参数指定的类型对象。
     * 改方法适用于查询SQL中使用命名参数的查询方式,命名参数名称与
     * 参数bean,即paramsBean中的属性名称必须一致。
     * 
     * @param querySql
     * @param paramsBean
     * @param clazzTo
     * @param sjt
     * @return
     */
    public static <T> List<T> queryForList(SimpleJdbcTemplate sjt, final Class<T> clazzTo, String querySql, Object paramsBean) {
    	ObjectUtil.assertParamsNotNULL(querySql, paramsBean, clazzTo, sjt);
    	sjt = createOrGetSJT(sjt);
		List<T> rms = sjt.query(querySql, new ParameterizedRowMapper<T>() {
			@SuppressWarnings("unchecked")
			public T mapRow(ResultSet rs,int rowNum) throws SQLException{	
				Object bean = null;
				if (isPrivimateType(clazzTo)) {
					bean = getPrivimateType(clazzTo, rs);
				} else {
				    bean = (Object) JdbcUtil.convert(rs, clazzTo);
				}
			    return (T)bean;
			}
		}, ObjectUtil.convert(paramsBean));		
		
		return rms;
	}
    
    /**
     * 返回一个查询列表, 列表中的对象类型为clazzTo参数指定的类型对象。
     * 改方法适用于查询SQL中使用命名参数的查询方式,命名参数名称与
     * 参数bean,即paramsBean中的属性名称必须一致。
     * 
     * @param querySql
     * @param paramsBean
     * @param clazzTo
     * @param dao
     * @return
     */
    public static <T> List<T> queryForList(final Class<T> clazzTo, String querySql, Object paramsBean, SimpleJdbcDaoSupport dao) {
    	return queryForList(dao.getSimpleJdbcTemplate(), clazzTo, querySql, paramsBean);
    }
    
    /**
     * MAP
     * 
     * @param sjt
     * @param querySql
     * @param paramsBean
     * @return
     */
    public static Map<String, Object> queryForMap(SimpleJdbcTemplate sjt, String querySql, Object paramsBean) {
    	sjt = createOrGetSJT(sjt);
    	Map<String, Object> results = null;
    	try {
    		results = sjt.queryForMap(querySql, ObjectUtil.convert(paramsBean));
    	} catch (EmptyResultDataAccessException e) {
    		// empty
    	}
    	
    	return results;
    }
    
    /**
     * MAP
     * 
     * @param sjt
     * @param querySql
     * @param args
     * @return
     */
    public static Map<String, Object> queryForMap(SimpleJdbcTemplate sjt, String querySql, Map<String, Object> args) {
    	sjt = createOrGetSJT(sjt);
    	Map<String, Object> results = null;
    	try {
    		results = sjt.queryForMap(querySql, args);
    	} catch (EmptyResultDataAccessException e) {
    		// empty
    	}
    	
    	return results;
    }
    
    /**
     * MAP
     * 
     * @param dao
     * @param querySql
     * @param args
     * @return
     */
    public static Map<String, Object> queryForMap(SimpleJdbcDaoSupport dao, String querySql, Map<String, Object> args) {
    	SimpleJdbcTemplate sjt = createOrGetSJT(dao.getSimpleJdbcTemplate());
    	Map<String, Object> results = null;
    	try {
    		results = sjt.queryForMap(querySql, args);
    	} catch (EmptyResultDataAccessException e) {
    		// empty
    	}
    	
    	return results;
    }
    
    /**
     * MAP
     * 
     * @param dao
     * @param querySql
     * @param paramsBean
     * @return
     */
    public static Map<String, Object> queryForMap(SimpleJdbcDaoSupport dao, String querySql, Object paramsBean) {
    	SimpleJdbcTemplate sjt = createOrGetSJT(dao.getSimpleJdbcTemplate());
    	Map<String, Object> results = null;
    	try {
    		results = sjt.queryForMap(querySql, ObjectUtil.convert(paramsBean));
    	} catch (EmptyResultDataAccessException e) {
    		// empty
    	}
    	
    	return results;
    }
    
    /**
     * MAP
     * 
     * @param dao
     * @param querySql
     * @param params
     * @return
     */
    public static Map<String, Object> queryForMap(SimpleJdbcDaoSupport dao, String querySql, Object...params) {
    	SimpleJdbcTemplate sjt = createOrGetSJT(dao.getSimpleJdbcTemplate());
    	Map<String, Object> results = null;
    	try {
    		results = sjt.queryForMap(querySql, params);
    	} catch (EmptyResultDataAccessException e) {
    		// empty
    	}
    	
    	return results;
    }
    
    /**
     * MAP
     * 
     * @param sjt
     * @param querySql
     * @param params
     * @return
     */
    public static Map<String, Object> queryForMap(SimpleJdbcTemplate sjt, String querySql, Object...params) {
    	sjt = createOrGetSJT(sjt);
    	Map<String, Object> results = null;
    	try {
    		results = sjt.queryForMap(querySql, params);
    	} catch (EmptyResultDataAccessException e) {
    		// empty
    	}
    	
    	return results;
    }
    
    /**
     * 转换参数对象为PSS
     * 
     * @param params
     * @return
     */
    private static PreparedStatementSetter translatePSSFor(Object...params) {
    	final TypeValue[] typeValues = ObjectUtil.convertToType(params);
    	if (typeValues == null || typeValues.length == 0) {
    		return null;
    	}
    	
    	// set参数
    	PreparedStatementSetter pss = new PreparedStatementSetter() {
    		public void setValues(PreparedStatement ps) throws SQLException {
    			for (int i = 1; i <= typeValues.length; i ++) {
    				TypeValue t = typeValues[i - 1];
    				Class<?> type = t.getType();
    				if (type == null) {
    					ps.setObject(i, null);
    					continue;
    				}
    				
    				Object value = t.getValue();
    				if (type.equals(String.class)) {
    					ps.setString(i, (String)value);
    				} else if (type.equals(Integer.class)) {
    					ps.setInt(i, (Integer)value);
    				} else if (type.equals(Short.class)) {
    					ps.setShort(i, (Short)value);
    				} else if (type.equals(Long.class)) {
    					ps.setLong(i, (Long)value);
    				} else if (type.equals(Float.class)) {
    					ps.setFloat(i, (Float)value);
    				} else if (type.equals(Double.class)) {
    					ps.setDouble(i, (Double)value);
    				} else if (type.equals(Boolean.class)) {
    					ps.setBoolean(i, (Boolean)value);
    				} else if (type.equals(Byte.class)) {
    					ps.setByte(i, (Byte)value);
    				} else if (type.equals(BigDecimal.class)) {
    					ps.setBigDecimal(i, (BigDecimal)value);
    				} else {
    					throw new SQLException("不支持的可变参数类型. type = " + type);
    				}
    			}
    		}
    	};
    	
    	return pss;
    }
    
    /**
     * 基本类型
     * 
     * @param type
     * @return
     */
    private static boolean isPrivimateType(Class<?> type) {
    	if (type.equals(String.class)) {
			return true;
        } else if (type.equals(Integer.TYPE) || type.equals(Integer.class)) {
        	return true;
        } else if (type.equals(Boolean.TYPE) || type.equals(Boolean.class)) {
        	return true;
        } else if (type.equals(Long.TYPE) || type.equals(Long.class)) {
        	return true;
        } else if (type.equals(Double.TYPE) || type.equals(Double.class)) {
        	return true;
        } else if (type.equals(Float.TYPE) || type.equals(Float.class)) {
        	return true;
        } else if (type.equals(Short.TYPE) || type.equals(Short.class)) {
        	return true;
        } else if (type.equals(Byte.TYPE) || type.equals(Byte.class)) {
        	return true;
        } else if (type.equals(BigDecimal.class)) {
        	return true;
        } else if (type.equals(BigInteger.class)) {
        	return true;
        }
    	
    	return false;
    }
    
    /**
     * 获取基本类型
     * 
     * @param type
     * @param rs
     * @return
     * @throws SQLException
     */
    private static Object getPrivimateType(Class<?> type, ResultSet rs) throws SQLException {
    	if (type.equals(String.class)) {
			return rs.getString(OBJECT_COLUME_INDEX);
        } else if (type.equals(Integer.TYPE) || type.equals(Integer.class)) {
        	return rs.getInt(OBJECT_COLUME_INDEX);
        } else if (type.equals(Boolean.TYPE) || type.equals(Boolean.class)) {
        	return rs.getBoolean(OBJECT_COLUME_INDEX);
        } else if (type.equals(Long.TYPE) || type.equals(Long.class)) {
        	return rs.getLong(OBJECT_COLUME_INDEX);
        } else if (type.equals(Double.TYPE) || type.equals(Double.class)) {
        	return rs.getDouble(OBJECT_COLUME_INDEX);
        } else if (type.equals(Float.TYPE) || type.equals(Float.class)) {
        	return rs.getFloat(OBJECT_COLUME_INDEX);
        } else if (type.equals(Short.TYPE) || type.equals(Short.class)) {
        	return rs.getShort(OBJECT_COLUME_INDEX);
        } else if (type.equals(Byte.TYPE) || type.equals(Byte.class)) {
        	return rs.getByte(OBJECT_COLUME_INDEX);
        } else if (type.equals(BigDecimal.class)) {
        	return rs.getBigDecimal(OBJECT_COLUME_INDEX);
        } else if (type.equals(BigInteger.class)) {
        	return rs.getInt(OBJECT_COLUME_INDEX);
        }
    	
    	return null;
    }
}
